/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.modules.anniversary.repository;

import me.zhengjie.modules.anniversary.domain.News;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
* @website https://el-admin.vip
* @author Jiayin Wei
* @date 2020-07-28
**/
public interface NewsRepository extends JpaRepository<News, Long>, JpaSpecificationExecutor<News> {
    /**
     * 查找首页模块最新3条资讯信息
     * @param pageable
     * @return
     */
    @Query(value = "select * from gzmu_ann_news WHERE verify_status=1 AND is_enable=1 ORDER BY sort DESC", nativeQuery = true)
    List<Map<String,Object>> findByNewest(Pageable pageable);

    /**
     * 查找首页模块资讯信息(分页)
     * @param pageable
     * @return
     */
    @Query(value = "select n.*,u.username from gzmu_ann_news n left join sys_user u on n.user_id=u.user_id WHERE n.ass_id is NULL AND n.verify_status=1 AND n.is_enable=1 ORDER BY n.create_time DESC", nativeQuery = true)
    List<Map<String,Object>> findByAllNews(Pageable pageable);

    /**
     * 查询校友会资讯信息
     * @param assId
     * @param pageable
     * @return
     */
    @Query(value = "select n.*,u.username from gzmu_ann_news n left join sys_user u on n.user_id=u.user_id WHERE n.ass_id=:assId AND n.verify_status=1 AND n.is_enable=1 ORDER BY n.create_time DESC", nativeQuery = true)
    List<Map<String,Object>> findByAllAlumniNews(Long assId,Pageable pageable);

    /**
     * 按ass_id查找资讯信息(分页)
     * @param pageable
     * @return
     */
    @Query(value = "select id,name titleName,content,create_time,name userName from gzmu_ann_news where ass_id=?1 and verify_status=1 and is_enable=1 ORDER BY create_time DESC",nativeQuery = true)
    List<Map<String,Object>> queryAssociationNewslimit(Long id, Pageable pageable);

    /**
     * 按ass_id查找资讯信息(分页)
     * @param id
     * @return
     */
    @Query(value = "select * from gzmu_ann_news where id=?1",nativeQuery = true)
    Map<String,Object> queryAssociationById(Long id);
}
